package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.RoomMapper;
import net.suncaper.mvc.model.domain.Room;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Room表数据库访问接口实现类
 */
public class RoomMapperImpl implements RoomMapper {
    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 添加房间
     */
    @Override
    public boolean insertRoom(Room room) {
        // 修正字段名：统一为下划线命名（RoomType→room_type, HotelName→hotel_name等）
        String sql = "INSERT INTO room (room_type, hotel_name, image_url, price, available_count, breakfast) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    room.getRoomType(),
                    room.getHotelName(),
                    room.getImageUrl(),
                    room.getPrice(),
                    room.getAvailableCount(),
                    room.getBreakfast()
            );
        } catch (SQLException e) {
            throw new RuntimeException("添加房间失败：" + e.getMessage());
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据酒店名称查询房间列表
     */
    @Override
    public List<Room> selectRoomsByHotel(String hotelName) {
        // 修正字段名：HotelName→hotel_name
        String sql = "SELECT * FROM room WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        List<Room> rooms = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, hotelName);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Room room = new Room();
                room.setRoomType(rs.getString("room_type"));
                room.setHotelName(rs.getString("hotel_name"));
                room.setImageUrl(rs.getString("image_url"));
                room.setPrice(rs.getBigDecimal("price"));
                room.setAvailableCount(rs.getInt("available_count"));
                room.setBreakfast(rs.getString("breakfast"));
                rooms.add(room);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询酒店房间失败：" + e.getMessage());
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return rooms;
    }

    /**
     * 根据房型和酒店名称查询唯一房间
     */
    @Override
    public Room selectRoomByTypeAndHotel(String roomType, String hotelName) {
        String sql = "SELECT * FROM room WHERE room_type = ? AND hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        Room room = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, roomType);
            pstmt.setString(2, hotelName);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                room = new Room();
                room.setRoomType(rs.getString("room_type"));
                room.setHotelName(rs.getString("hotel_name"));
                room.setImageUrl(rs.getString("image_url"));
                room.setPrice(rs.getBigDecimal("price"));
                room.setAvailableCount(rs.getInt("available_count"));
                room.setBreakfast(rs.getString("breakfast"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询房间失败：" + e.getMessage());
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return room;
    }

    /**
     * 更新房间信息
     */
    @Override
    public boolean updateRoom(Room room) {
        // 修正字段名：ImageUrl→image_url, Price→price等，条件字段统一为下划线命名
        String sql = "UPDATE room SET image_url = ?, price = ?, available_count = ?, breakfast = ? " +
                "WHERE room_type = ? AND hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    room.getImageUrl(),
                    room.getPrice(),
                    room.getAvailableCount(),
                    room.getBreakfast(),
                    room.getRoomType(),
                    room.getHotelName()
            );
        } catch (SQLException e) {
            throw new RuntimeException("更新房间失败：" + e.getMessage());
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除房间
     */
    @Override
    public boolean deleteRoom(String roomType, String hotelName) {
        // 修正字段名：RoomType→room_type, HotelName→hotel_name
        String sql = "DELETE FROM room WHERE room_type = ? AND hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    roomType,
                    hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException("删除房间失败：" + e.getMessage());
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 查询所有房间
     */
    @Override
    public List<Room> selectAllRooms() {
        // 表名改为小写room
        String sql = "SELECT * FROM room";
        Connection connection = JDBCUtils.getConnection();
        List<Room> rooms = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Room room = new Room();
                room.setRoomType(rs.getString("room_type"));
                room.setHotelName(rs.getString("hotel_name"));
                room.setImageUrl(rs.getString("image_url"));
                room.setPrice(rs.getBigDecimal("price"));
                room.setAvailableCount(rs.getInt("available_count"));
                room.setBreakfast(rs.getString("breakfast"));
                rooms.add(room);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询所有房间失败：" + e.getMessage());
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return rooms;
    }

    /**
     * 创建订单时可用数量-1（每个订单对应1个房间）
     */
    @Override
    public boolean decreaseAvailableCountByOne(String roomType, String hotelName) {
        // 修正字段名：AvailableCount→available_count，条件字段统一为下划线命名
        String sql = "UPDATE room SET available_count = available_count - 1 " +
                "WHERE room_type = ? AND hotel_name = ? AND available_count >= 1";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    roomType,
                    hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException("创建订单时减少房间库存失败：" + e.getMessage());
        } finally {
            JDBCUtils.close();
        }
        return rows > 0; // rows=1表示成功减少，rows=0表示库存不足或房间不存在
    }

    /**
     * 取消订单时可用数量+1
     */
    @Override
    public boolean increaseAvailableCountByOne(String roomType, String hotelName) {
        // 修正字段名：AvailableCount→available_count，条件字段统一为下划线命名
        String sql = "UPDATE room SET available_count = available_count + 1 " +
                "WHERE room_type = ? AND hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    roomType,
                    hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException("取消订单时增加房间库存失败：" + e.getMessage());
        } finally {
            JDBCUtils.close();
        }
        return rows > 0; // rows=1表示成功增加，rows=0表示房间不存在
    }
    
    /**
     * 关闭资源
     * 
     * @param rs ResultSet对象
     * @param stmt PreparedStatement对象
     */
    private void closeResources(ResultSet rs, PreparedStatement stmt) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}